package pers.lance.xml.db.dao;

import pers.lance.xml.db.bean.Student;
import pers.lance.xml.db.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Lance Li
 * @date 7/10/2018 6:49 PM
 * @desc
 */
public class StudentDaoImp implements BasicDao<Student> {
    public int add(Student student) {
        String sql = "insert into students (id, name, sex, classes, age, school) values(?,?,?,?,?,?)";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setStudentStatement(pstmt, student);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public int update(Student student) {
        String sql = " update students set name = ?, sex = ?, classes = ?, age = ?, school = ? where id = ?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            setStudentStatement(pstmt, student);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public int delete(Student student) {
        String sql = " delete students where id = ?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, student.getId());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return 0;
    }

    public Student load(int id) {
        String sql = " select  * from students where id = ?";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Student> students = new ArrayList<Student>();
            while (rs.next()) {
                return createStudent(rs);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }

    public int[] addStudents(List<Student> students) {
        String sql = "insert into students (id, name, sex, classes, age, school) values(?,?,?,?,?,?)";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            for (Student student : students) {
                setStudentStatement(pstmt, student);
                pstmt.addBatch();
            }
            return pstmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }

    public List<Student> findAll() {
        String sql = " select * from students";
        Connection conn = DBUtil.getConnection();
        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Student> students = new ArrayList<Student>();
            while (rs.next()) {
                Student student = createStudent(rs);
                students.add(student);
            }
            return students;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        return null;
    }

    public void setStudentStatement(PreparedStatement statement, Student student) throws SQLException {
        statement.setInt(1, student.getId());
        statement.setString(2, student.getName());
        statement.setString(3, student.getSex());
        statement.setString(4, student.getClasses());
        statement.setInt(5, student.getAge());
        statement.setString(6, student.getSchool());
    }

    public Student createStudent(ResultSet rs) throws SQLException {
        int sid = rs.getInt("id");
        String name = rs.getString("name");
        String sex = rs.getString("sex");
        int age = rs.getInt("age");
        String classes = rs.getString("classes");
        String school = rs.getString("school");
        return new Student(sid, name, sex, classes, age, school);
    }
}
